﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DIProject.Data
{
   public class Goods
    {
       private static DataSets.Dictionary.vwGoodsDetailedDataTable _data = null;
       public static DataSets.Dictionary.vwGoodsDetailedDataTable Data
       {
           get
           {
               if (_data == null)
               {
                   DataSets.DictionaryTableAdapters.vwGoodsDetailedTableAdapter adapter = new DIProject.DataSets.DictionaryTableAdapters.vwGoodsDetailedTableAdapter();
                   _data = adapter.GetData();
               }
               return _data;
           }
       }
       public static void ClearData()
       {
           _data = null;
       }
       /// <summary>
       /// возврашает идентификатор родителя если таковой есть.
       /// </summary>
       /// <param name="goodsId"></param>
       /// <returns></returns>
       public static Int32? GetParentId(Int32 goodsId)
       {
           Int32? result = null;
           DataSets.UtilTableAdapters.UtilQueries adapter = new DIProject.DataSets.UtilTableAdapters.UtilQueries();
           object obj = adapter.GoodChildId(goodsId);
           if (obj is DBNull)
               return result;
           if (obj is Int32)
               result = (Int32)obj;
           return result;
       }
       public static Int32 GetCatalog(Int32 goodsId)
       {
           string QueryString = 
@"SELECT CA.Assortment.CatalogId
FROM CA.Goods INNER JOIN
CA.Assortment ON CA.Goods.AssortmentId = CA.Assortment.Id
WHERE (CA.Goods.Id = @Id)";
           string connStr = Properties.Settings.Default.DIAptekaConnectionString;
           try
           {
               using (SqlConnection connection = new SqlConnection(connStr))
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   SqlParameter gId = new SqlParameter("@Id", SqlDbType.Int);
                   gId.Value = goodsId;
                   command.Parameters.Add(gId);
                   connection.Open();
                   object obj = command.ExecuteNonQuery();
                   if (Convert.IsDBNull(obj) || (obj == null))
                   {
                       throw new Util.DIException("Не удалось определить каталог для товара");
                   }
                   return (Int32)obj;
               }
           }
           catch (SqlException se)
           {
               throw new Util.DIException("Не удалось определить каталог для товара",se);
           }
           catch (Exception ee)
           {
               throw new Util.DIException("Не удалось определить каталог для товара", ee);
           }
       }
       /// <summary>
       /// вставляем новый товар в БД и возвращаем его идентификатор
       /// </summary>
       /// <param name="assortmentId"></param>
       /// <param name="manufacturerId"></param>
       /// <param name="countryId"></param>
       /// <param name="wholesalePack"></param>
       /// <param name="retailPack"></param>
       /// <param name="childId"></param>
       /// <param name="allowUnPack"></param>
       /// <param name="manufactureBarCode"></param>
       /// <param name="fixedPrice"></param>
       /// <param name="defekturaAggregate"></param>
       /// <param name="note"></param>
       /// <param name="mD"></param>
       /// <param name="mW"></param>
       /// <returns></returns>
       public static Int32 NewGood(Int32 assortmentId, Int32 manufacturerId, Int32 countryId,
           Int32 wholesalePack, Int32 retailPack, Int32? childId, bool allowUnPack, string manufactureBarCode,
           decimal? fixedPrice, bool defekturaAggregate, string note, DateTime mD, string mW)
       { /*@AssortmentId int,
	@ManufacturerId int,
	@CountryId int,
	@WholesalePack int,
	@RetailPack int,
	@ChildId int,
	@AllowUnPack bit,
	@ManufactureBarCode varchar(13),
	@FixedPrice decimal(19, 2), --Розничная цена по умолчанию
	@DefekturaAggregate bit,
	@Note varchar(250),
	@MD datetime,
	@MW varchar(50)*/
           string QueryString = "dbo.GoodsInsert";
           string connStr = Properties.Settings.Default.DIAptekaConnectionString;
           Int32 tmp = Goods.IfExist(assortmentId, manufacturerId, countryId, retailPack);
           if (tmp != Int32.MinValue)
               return tmp;
           using (SqlConnection connection = new SqlConnection(connStr))
           {
               try
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   command.CommandType = CommandType.StoredProcedure;
                   SqlParameter par = new SqlParameter("@AssortmentId", assortmentId);
                   command.Parameters.Add(par);
                   par = new SqlParameter("@ManufacturerId", manufacturerId);
                   command.Parameters.Add(par);
                   par = new SqlParameter("@CountryId", countryId);
                   command.Parameters.Add(par);
                   command.Parameters.AddWithValue("@WholesalePack", wholesalePack);
                   command.Parameters.AddWithValue("@RetailPack", retailPack);
                   if (childId.HasValue)
                       command.Parameters.AddWithValue("@ChildId", childId);
                   else
                       command.Parameters.AddWithValue("@ChildId", DBNull.Value);
                   command.Parameters.AddWithValue("@AllowUnPack", allowUnPack);
                   if (manufactureBarCode == null)
                       command.Parameters.AddWithValue("@ManufactureBarCode", DBNull.Value);
                   else
                       command.Parameters.AddWithValue("@ManufactureBarCode", manufactureBarCode);
                   if(fixedPrice.HasValue)
                       command.Parameters.AddWithValue("@FixedPrice", fixedPrice);
                   else
                       command.Parameters.AddWithValue("@FixedPrice", DBNull.Value);
                   command.Parameters.AddWithValue("@DefekturaAggregate", defekturaAggregate);
                   command.Parameters.AddWithValue("@Note", note);
                   command.Parameters.AddWithValue("@MD", mD);
                   command.Parameters.AddWithValue("@MW", mW);
                   connection.Open();
                   SqlDataReader dr = command.ExecuteReader();
                   Int32 id = dr.GetOrdinal("Id");
                   if (dr.Read())
                   {
                       if (dr.IsDBNull(id))
                       {
                           throw new Util.DIException("Не удалось добавить товар");
                       }
                       Int32 result = dr.GetInt32(id);
                       return result;
                   }
                   else
                   {
                       throw new Util.DIException("Не удалось добавить товар");
                   }
               }
               catch (SqlException se)
               {
                   throw new Util.DIException("Ошибка при добавлении товара", se);
               }
               catch (Exception ee)
               {
                   throw new Util.DIException("Ошибка при добавлении товара", ee);
               }
           }
       
       }
       /// <summary>
       /// проверяем, есть ли уже такой товар
       /// </summary>
       /// <param name="assortmentId"></param>
       /// <param name="manufacturerId"></param>
       /// <param name="countryId"></param>
       /// <param name="retailPack"></param>
       /// <returns></returns>
       public static Int32 IfExist(Int32 assortmentId, Int32 manufacturerId, Int32 countryId, Int32 retailPack)
       {
           string QueryString = @"select Id from CA.Goods
where AssortmentId = @AssortmentId and ManufacturerId = @ManufacturerId
and CountryId = @CountryId and RetailPack = @RetailPack";
           string connStr = Properties.Settings.Default.DIAptekaConnectionString;
           using (SqlConnection connection = new SqlConnection(connStr))
           {
               try
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   SqlParameter par = new SqlParameter("@AssortmentId", SqlDbType.Int);
                   par.Value = assortmentId;
                   command.Parameters.Add(par);
                   par = new SqlParameter("@ManufacturerId", manufacturerId);
                   command.Parameters.Add(par);
                   par = new SqlParameter("@CountryId", countryId);
                   command.Parameters.Add(par);
                   par = new SqlParameter("@RetailPack", retailPack);
                   command.Parameters.Add(par);
                   connection.Open();
                   object obj = command.ExecuteScalar();
                   if (Convert.IsDBNull(obj) || obj == null)
                   {
                       return Int32.MinValue;
                   }
                   return Convert.ToInt32(obj);
               }
               catch (SqlException se)
               {
                   throw new Util.DIException("Ошибка при проверке товара", se);
               }
               catch (Exception ee)
               {
                   throw new Util.DIException("Ошибка при проверке товара", ee);
               }
           }
       }
       public static Int32 GoodsModifi(Int32 assortmentId, Int32 manufacturerId,Int32 countryId,
           Int32 wholesalePack, Int32 retailPack, Int32? childId, bool allowUnPack, string manufactureBarCode,
           decimal? fixedPrice, bool toDelete, bool defekturaAggregate, string note, DateTime mD, string mW,
           Int32 original_Id, DateTime original_MD
           )
       {
           string QueryString = "dbo.GoodsUpdate";
           string connStr = Properties.Settings.Default.DIAptekaConnectionString;
           using (SqlConnection connection = new SqlConnection(connStr))
           {
               try
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   command.CommandType = CommandType.StoredProcedure;
                   SqlParameter par = new SqlParameter("@AssortmentId", assortmentId);
                   command.Parameters.Add(par);
                   par = new SqlParameter("@ManufacturerId", manufacturerId);
                   command.Parameters.Add(par);
                   par = new SqlParameter("@CountryId", countryId);
                   command.Parameters.Add(par);
                   command.Parameters.AddWithValue("@WholesalePack", wholesalePack);
                   command.Parameters.AddWithValue("@RetailPack", retailPack);
                   if (childId.HasValue)
                       command.Parameters.AddWithValue("@ChildId", childId);
                   else
                       command.Parameters.AddWithValue("@ChildId", DBNull.Value);
                   command.Parameters.AddWithValue("@AllowUnPack", allowUnPack);
                   command.Parameters.AddWithValue("@ManufactureBarCode", manufactureBarCode);
                   if (fixedPrice.HasValue)
                       command.Parameters.AddWithValue("@FixedPrice", fixedPrice);
                   else
                       command.Parameters.AddWithValue("@FixedPrice", DBNull.Value);
                   command.Parameters.AddWithValue("@DefekturaAggregate", defekturaAggregate);
                   if (note != null)
                       command.Parameters.AddWithValue("@Note", note);
                   else
                       command.Parameters.AddWithValue("Note", DBNull.Value);
                   command.Parameters.AddWithValue("@MD", mD);
                   command.Parameters.AddWithValue("@MW", mW);
                   command.Parameters.AddWithValue("@ToDelete", toDelete);
                   command.Parameters.AddWithValue("@Original_Id", original_Id);
                   command.Parameters.AddWithValue("@Original_MD", original_MD);
                   connection.Open();
                   SqlDataReader dr = command.ExecuteReader();
                   Int32 id = dr.GetOrdinal("Id");
                   if (dr.Read())
                   {
                       if (dr.IsDBNull(id))
                       {
                           throw new Util.DIException("Не удалось добавить товар");
                       }
                       Int32 result = dr.GetInt32(id);
                       return result;
                   }
                   else
                   {
                       throw new Util.DIException("Не удалось добавить товар");
                   }
               }
               catch (SqlException se)
               {
                   throw new Util.DIException("Ошибка при изменении товара", se);
               }
               catch (Exception ee)
               {
                   throw new Util.DIException("Ошибка при изменении товара", ee);
               }
           }
           /*@AssortmentId int,
	@ManufacturerId int,
	@CountryId int,
	@WholesalePack int,
	@RetailPack int,
	@ChildId int,
	@AllowUnPack bit,
	@ManufactureBarCode varchar(13),
	@FixedPrice decimal(19, 2), --Розничная цена по умолчанию
	@ToDelete bit,
	@DefekturaAggregate bit,
	@Note varchar(250),
	@MD datetime,
	@MW varchar(50),
	@Original_Id int,
	@Original_MD datetime*/
       }
       public static Int32 GoodsDelete(Int32 id, DateTime mD, string mW)
       {
           string QueryString = "dbo.GoodsDelete";
           string conStr = Properties.Settings.Default.DIAptekaConnectionString;
           using (SqlConnection connection = new SqlConnection(conStr))
           {
               try
               {
                   SqlCommand command = new SqlCommand(QueryString, connection);
                   command.CommandType = CommandType.StoredProcedure;
                   command.Parameters.AddWithValue("@Original_Id", id);
                   command.Parameters.AddWithValue("@Original_MD", mD);
                   command.Parameters.AddWithValue("@MW", mW);
                   connection.Open();
                   Int32 res = command.ExecuteNonQuery();
                   return res;
               }
               catch (SqlException se)
               {
                   throw new Util.DIException("Ошибка при удалении товара", se);
               }
               catch (Exception ee)
               {
                   throw new Util.DIException("Ошибка при удалении товара", ee);
               }
           }
       }
       
    }
    public class GoodsDetails
    {
        private Int32 _id;

        public Int32 Id
        {
            get { return _id; }
            set { _id = value; }
        }
        //FullName, ManufacturerName, CountryName, RetailPack
        private string _FullName;

        public string FullName
        {
            get { return _FullName; }
            set { _FullName = value; }
        }
        private string _ManufacturerName;

        public string ManufacturerName
        {
            get { return _ManufacturerName; }
            set { _ManufacturerName = value; }
        }

        private string _CountryName;

        public string CountryName
        {
            get { return _CountryName; }
            set { _CountryName = value; }
        }
        private Int32 _RetailPack;

        public Int32 RetailPack
        {
            get { return _RetailPack; }
            set { _RetailPack = value; }
        }
        public static List<GoodsDetails> GetTop200Goods(string filtr)
        {
            string QueryString = @"
SELECT     TOP (200) Id, FullName, ManufacturerName, CountryName, RetailPack
FROM         vwGoodsDetailed
WHERE     (FullName LIKE @tmp)
ORDER BY Id";
//ORDER BY FullName";
            string newFiltr = filtr.Replace("%", string.Empty);
            newFiltr = "%" + newFiltr + "%";
            List<GoodsDetails> res = new List<GoodsDetails>(200);
            try
            {
                using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.DIAptekaConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter p = new SqlParameter("@tmp", SqlDbType.VarChar, 213);
                    p.Value = newFiltr;
                    command.Parameters.Add(p);
                    connection.Open();
                    SqlDataReader r = command.ExecuteReader();
                    Int32 iId = r.GetOrdinal("Id");
                    Int32 iFullName = r.GetOrdinal("FullName");
                    Int32 iManufacturerName = r.GetOrdinal("ManufacturerName");
                    Int32 iCountryName = r.GetOrdinal("CountryName");
                    Int32 iRetailPack = r.GetOrdinal("RetailPack");
                    while (r.Read())
                    {
                        GoodsDetails gd = new GoodsDetails();
                        gd.Id = r.GetInt32(iId);
                        gd.FullName = r.GetString(iFullName);
                        gd.ManufacturerName = r.GetString(iManufacturerName);
                        gd.CountryName = r.GetString(iCountryName);
                        gd.RetailPack = r.GetInt32(iRetailPack);
                        res.Add(gd);
                    }
                    return res;
                }
            }
            catch (SqlException se)
            {
                string s = se.ToString();
                throw se;
            }
            catch (Exception ee)
            {
                string s = ee.ToString();
                throw ee;
            }
        }
    }
}
